﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SQLite;

namespace PhoneLocationTracker
{
    class Program
    {
        /// <summary>
        /// Main entry point
        /// </summary>
        /// <param name="args">Command line args.</param>
        static void Main(string[] args)
        {
            if (args.Length != 2)
            {
                Console.WriteLine("Parses consolidated.db SQLite database file.");
                Console.WriteLine("Creates an HTML file that contains all locations");
                Console.WriteLine("outputed to google maps as markers.");
                Console.WriteLine("Wrong Number of Arguments");
                Console.WriteLine("Syntax:");
                Console.WriteLine("    PhoneLocationTracker <database file path> <output file path>");
            }
            else
            {
                try
                {
                    CreateMap(args[0], args[1]);
                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message);
                }
            }
        }

        /// <summary>
        /// Load DMap.htm and replaces {markers} with data from consolidated.db
        /// </summary>
        /// <param name="dbFile">The databse file path.</param>
        /// <param name="outFile">The output file path.</param>
        public static void CreateMap(string dbFile, string outFile)
        {
            // Verizon stores all location data in CdmaCellLocation table
            DataTable geo = GetDataTable(dbFile, "SELECT Latitude, Longitude, Timestamp FROM CdmaCellLocation ORDER BY Timestamp DESC;");

            if (geo.Rows.Count < 100)
            {
                // Att stores all location data in CellLocation table
                geo = GetDataTable(dbFile, "SELECT Latitude, Longitude, Timestamp FROM CellLocation ORDER BY Timestamp DESC;");
            }

            if (geo.Rows.Count > 0)
            {
                TextWriter tw = new StreamWriter(outFile);
                // Cocoa time beging Jan 1st 2001
                DateTime dt = new DateTime(2001, 1, 1, 0, 0, 0);

                // Read the template file
                using (StreamReader sr = new StreamReader("DMap.htm"))
                {
                    String line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        if (line.Contains("{markers}"))
                        {
                            // Wrtie markers
                            foreach (DataRow r in geo.Rows)
                            {
                                Double lat = (double)r["Latitude"];
                                Double lng = (double)r["Longitude"];
                                Double timestamp = (double)r["Timestamp"];

                                // Timestamp is in seconds UTC 
                                DateTime local = TimeZoneInfo.ConvertTimeFromUtc(dt.AddSeconds(timestamp), TimeZoneInfo.Local);

                                // Add a Javascript marker to google maps
                                tw.WriteLine("myLatlng = new google.maps.LatLng(" + lat.ToString() + ", " + lng.ToString() + ");");
                                tw.WriteLine("marker = new google.maps.Marker({");
                                tw.WriteLine("    position: myLatlng,");
                                tw.WriteLine("    title:\"" + local.ToString() + "\"");
                                tw.WriteLine("});");
                                tw.WriteLine("marker.setMap(map);");
                            }
                        }
                        else
                        {
                            tw.WriteLine(line);
                        }
                    }
                }

                tw.Close();
            }
        }

        /// <summary>
        /// Gets a data table from the database.
        /// </summary>
        /// <param name="dbFile">The database file path.</param>
        /// <param name="sql">The SQL statement to run.</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string dbFile, string sql)
        {

            DataTable dt = new DataTable();

            try
            {

                SQLiteConnection cnn = new SQLiteConnection("Data Source=" + dbFile);

                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = sql;

                SQLiteDataReader reader = mycommand.ExecuteReader();

                dt.Load(reader);

                reader.Close();

                cnn.Close();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return dt;
        }

    }
}
